package com.demo.util;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class MyDBUtils {

	static QueryRunner qr = new QueryRunner();

	public static void update() {
		int nums = 0;
		try {
			nums = qr.update(JDBCUtils.getConnection(),
					"INSERT INTO t_student(sno,sname,birthday,home,sex,blood) values (?,?,?,?,?,?)", "200", "tom",
					"1990-09-07", "四川成都", "男", "AB");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("影响的行数：" + nums);
	}

	// 查询单条记录
	public static Student get() {
		Student student = null;
		try {
			student = qr.query(JDBCUtils.getConnection(), "select * from t_student where sno=?",
					new BeanHandler<Student>(Student.class), "200");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return student;
	}

	// 查询多条记录
	public static List<Student> list() {
		List<Student> students = null;
		try {
			students = qr.query(JDBCUtils.getConnection(), "select * from t_student where 1=1",
					new BeanListHandler<Student>(Student.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return students;
	}

	// 查询单行单列的记录 比如统计函数
	public static Long count() {
		Long nums = null;
		try {
			nums = qr.query(JDBCUtils.getConnection(), "select count(1) as nums from t_student where 1=1",
					new ScalarHandler<Long>());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return nums;
	}

	// 连表查询，处理多张表的情况(范例：查询某个课程的所有分数、课程名和代号)
	public static List<Map<String, Object>> queryScoreByCno1() {
		List<Map<String, Object>> data = null;
		try {
			data = qr.query(JDBCUtils.getConnection(),
					"select c.cno,c.cname,s.score from t_course c, t_score s where c.cno=s.cno and s.cno=?",
					new MapListHandler(), "3-105");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return data;
	}

	// 连表查询，处理多张表的情况(范例：查询某个课程的所有分数、课程名和代号)
	public static List<CourseScoreDTO> queryScoreByCno2() {
		List<CourseScoreDTO> data = null;
		try {
			data = qr.query(JDBCUtils.getConnection(),
					"select c.cno,c.cname,s.score from t_course c, t_score s where c.cno=s.cno and s.cno=?",
					new BeanListHandler<CourseScoreDTO>(CourseScoreDTO.class), "3-105");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return data;
	}

	public static void main(String[] args) {
		// MyDBUtils.update();
		// System.out.println(MyDBUtils.get().toString());

		// List<Student> list = MyDBUtils.list();
		// System.out.println(MyDBUtils.count());
		MyDBUtils.queryScoreByCno2();

	}
}
